﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
using Models;

namespace DAL
{
    public class RoomManager
    {
        /// <summary>
        /// 获取房间信息(全部)
        /// </summary>
        /// <returns></returns>
        public List<Models.RoomInfo> GetRoomInfo()
        {
            string sql = "select tb_roomInfo.楼层,tb_roomInfo.房号,tb_roomInfo.类型ID,类型名称,tb_roomInfo.状态ID,状态名称,tb_roomInfo.备注 from tb_roomInfo,tb_roomType,tb_roomState where tb_roomInfo.类型ID=tb_roomType.类型ID and tb_roomInfo.状态ID=tb_roomState.状态ID";
            
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<Models.RoomInfo> list = new List<Models.RoomInfo>();
            while (rd.Read())
            {
                list.Add(new Models.RoomInfo
                {
                    FloorNum = Convert.ToInt32(rd["楼层"]),
                    RoomNum = rd["房号"].ToString(),
                    RoomTypeID = Convert.ToInt32(rd["类型ID"]),
                    RoomTypeName = rd["类型名称"].ToString(),
                    RoomStateID = Convert.ToInt32(rd["状态ID"]),
                    RoomStateName = rd["状态名称"].ToString(),
                    Remarks = rd["备注"].ToString(),
                });
            }
            return list;
        }


        /// <summary>
        /// 获取全部房间信息dataset
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllRoomInfoDataSet()
        {
            string sql = "select tb_roomInfo.楼层,tb_roomInfo.房号,类型名称,tb_roomInfo.类型ID,状态名称,tb_roomInfo.状态ID,tb_roomInfo.备注 from tb_roomInfo,tb_roomType,tb_roomState where tb_roomInfo.类型ID=tb_roomType.类型ID and tb_roomInfo.状态ID=tb_roomState.状态ID";
            return SQLHelper.GetDataSet(sql,"allRoomInfo");
        }


        /// <summary>
        /// 判断某房间是否有客
        /// </summary>
        /// <param name="roomNum">房号</param>
        /// <returns></returns>
        //public bool IsHaveCustomer(string roomNum)
        //{
        //    string sql = string.Format("select * from tb_customerInfo where 入住房号='{0}'",roomNum);
        //    SQLiteDataReader rd = SQLHelper.GetReader(sql);
        //    if (rd.HasRows)
        //    {
        //        rd.Close();
        //        return true;
        //    }
        //    else
        //    {
        //        rd.Close();
        //        return false;
        //    }
        //}


        /// <summary>
        /// 获取房间类型list
        /// </summary>
        /// <returns></returns>
        public List<RoomType> GetRoomType()
        {
            string sql = "select * from tb_roomType";
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<RoomType> list = new List<RoomType>();
            while (rd.Read())
            {
                list.Add(new RoomType { 
                    TypeID=Convert.ToInt32(rd["类型ID"]),
                    TypeName=rd["类型名称"].ToString(),
                    UnitPrice=Convert.ToDouble(rd["单价"]),
                });
            }
            rd.Close();
            return list;
        }


        /// <summary>
        /// 获取房间状态list
        /// </summary>
        /// <returns></returns>
        public List<RoomState> GetRoomState()
        {
            string sql = "select * from tb_roomState";
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<RoomState> list = new List<RoomState>();
            while (rd.Read())
            {
                list.Add(new RoomState
                {
                    StateID = Convert.ToInt32(rd["状态ID"]),
                    StateName = rd["状态名称"].ToString(),
                });
            }
            rd.Close();
            return list;
        }


        /// <summary>
        /// 获取对应楼层房间信息
        /// </summary>
        /// <param name="floorNum"></param>
        /// <returns>楼层</returns>
        public List<Models.RoomInfo> GetRoomInfo(int floorNum)
        {
            string sql = string.Format("select * from (select tb_roomInfo.楼层,tb_roomInfo.房号,tb_roomInfo.类型ID,tb_roomType.类型名称,tb_roomType.单价,tb_roomInfo.状态ID,状态名称,tb_roomInfo.备注 from tb_roomInfo,tb_roomType,tb_roomState where tb_roomInfo.类型ID=tb_roomType.类型ID and tb_roomInfo.状态ID=tb_roomState.状态ID) where 楼层={0}", floorNum);

            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<Models.RoomInfo> list = new List<Models.RoomInfo>();
            while (rd.Read())
            {
                list.Add(new Models.RoomInfo
                {
                    FloorNum = Convert.ToInt32(rd["楼层"]),
                    RoomNum = rd["房号"].ToString(),
                    RoomTypeID = Convert.ToInt32(rd["类型ID"]),
                    RoomTypeName = rd["类型名称"].ToString(),
                    UnitPrice=Convert.ToDouble(rd["单价"]),
                    RoomStateID = Convert.ToInt32(rd["状态ID"]),
                    RoomStateName = rd["状态名称"].ToString(),
                    Remarks = rd["备注"].ToString(),
                });
            }
            return list;
        }


        /// <summary>
        /// 获取 房号 对应房间详细信息
        /// </summary>
        /// <param name="roomNum"></param>
        /// <returns>房号</returns>
        public List<Models.RoomInfo> GetRoomInfo(string roomNum)
        {
            string sql = string.Format("select tb_roomInfo.楼层,tb_roomInfo.房号,tb_roomInfo.类型ID,类型名称,tb_roomType.单价,tb_roomInfo.状态ID,状态名称,tb_roomInfo.备注 from tb_roomInfo,tb_roomType,tb_roomState where tb_roomInfo.类型ID=tb_roomType.类型ID and tb_roomInfo.状态ID=tb_roomState.状态ID and 房号='{0}'", roomNum);

            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<Models.RoomInfo> list = new List<Models.RoomInfo>();
            while (rd.Read())
            {
                list.Add(new Models.RoomInfo
                {
                    FloorNum = Convert.ToInt32(rd["楼层"]),
                    RoomNum = rd["房号"].ToString(),
                    RoomTypeID = Convert.ToInt32(rd["类型ID"]),
                    RoomTypeName = rd["类型名称"].ToString(),
                    UnitPrice=Convert.ToDouble(rd["单价"]),
                    RoomStateID = Convert.ToInt32(rd["状态ID"]),
                    RoomStateName = rd["状态名称"].ToString(),
                    Remarks = rd["备注"].ToString(),
                });
            }
            return list;
        }


        /// <summary>
        /// 插入房间信息数据
        /// </summary>
        /// <param name="roomInfo"></param>
        /// <returns></returns>
        public int InsertRoomInfo(RoomInfo roomInfo)
        {
            string sql = string.Format("insert into tb_roomInfo (楼层,房号,类型ID,状态ID,备注) values({0},'{1}',{2},{3},'{4}')", roomInfo.FloorNum, roomInfo.RoomNum, roomInfo.RoomTypeID, roomInfo.RoomStateID, roomInfo.Remarks);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 更新房间信息（类型、状态、备注）
        /// </summary>
        /// <param name="roomInfo"></param>
        /// <returns></returns>
        public int UpdateRoomInfo(RoomInfo roomInfo)
        {
            string sql = string.Format("update tb_roomInfo set 类型ID={0},状态ID={1},备注='{2}' where 房号='{3}'", roomInfo.RoomTypeID, roomInfo.RoomStateID,roomInfo.Remarks,roomInfo.RoomNum);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 更新房间状态
        /// </summary>
        /// <param name="roomNum">房号</param>
        /// <param name="roomStateID">状态ID</param>
        /// <returns></returns>
        public int UpdateRoomState(string roomNum ,int roomStateID)
        {
            string sql = string.Format("update tb_roomInfo set 状态ID={0} where 房号='{1}'", roomStateID, roomNum);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 更新房间类型单价
        /// </summary>
        /// <param name="roomTypeID">房间类型ID</param>
        /// <param name="unitPrice">单价</param>
        /// <returns></returns>
        public int UpdateRoomTypeUnitPrice(int roomTypeID, double unitPrice)
        {
            string sql = string.Format("update tb_roomType set 单价={0} where 类型ID={1}", unitPrice, roomTypeID);
            return SQLHelper.Update(sql);
        }

    }
}
